package sanmubird.club.sql;

import com.google.common.collect.Streams;
import org.springframework.data.util.Pair;

import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author sam
 * @date 2021/4/20 20:43
 */
public class SqlBuilder {

    public static Object convert(String dateType, String value) {
        switch (dateType) {
            case "int":
            case "tinyint":
                return Integer.parseInt(value);
            case "double":
                return Double.parseDouble(value);
            case "date":
            case "varchar":
            default:
                return "'" + value + "'";
        }
    }

    public static String buildBatchInsertSql(Map<String, String> dataTypeMap, List<Map<String, String>> rowDataMapList) {
        List<Pair<String, Object>> firstList = assembleNameValuePairList(dataTypeMap, rowDataMapList.get(0));
        String columnSql = firstList.stream().map(Pair::getFirst).map(String::valueOf).collect(Collectors.joining(", ", " ( ", " ) "));

        String valuesSql = rowDataMapList.stream()
                .map(
                        rowDataMap -> {
                            List<Pair<String, Object>> columnNameValueList = assembleNameValuePairList(dataTypeMap, rowDataMap);
                            return columnNameValueList.stream().map(Pair::getSecond).map(String::valueOf).collect(Collectors.joining(", ", " ( ", " ) "));
                        }
                )
                .collect(Collectors.joining(", "));

        return "INSERT INTO table_name " + columnSql + "  VALUES " + valuesSql;
    }

    public static List<Pair<String, Object>> assembleNameValuePairList(Map<String, String> dataTypeMap, Map<String, String> rowDataMap) {
        return dataTypeMap.entrySet().stream()
                .map(
                        entry -> {
                            String columnName = entry.getKey();
                            String dataType = entry.getValue();
                            String columnValue = rowDataMap.get(columnName);
                            Object convertValue = convert(dataType, columnValue);
                            return Pair.of(columnName, convertValue);
                        }
                ).collect(Collectors.toList());
    }

    public static String buildBatchUpdateSql(Map<String, String> dataTypeMap, List<Map<String, String>> rowDataMapList, String primaryKey) {
//        UPDATE tableName
//    SET orderId = CASE id
//        WHEN 1 THEN 3
//        WHEN 2 THEN 4
//        WHEN 3 THEN 5
//    END
//WHERE id IN (1,2,3)

        String whereSql = rowDataMapList.stream()
                .map(
                        rowDataMap -> {
                            String dataType = dataTypeMap.get(primaryKey);
                            String value = rowDataMap.get(primaryKey);
                            return convert(dataType, value);
                        }
                )
                .map(String::valueOf)
                .collect(Collectors.joining(", ", primaryKey + " IN (", ")"));


        List<Pair<String, Object>> firstList = assembleNameValuePairList(dataTypeMap, rowDataMapList.get(0));
        List<String> columnNameList = firstList.stream().map(Pair::getFirst).collect(Collectors.toList());
        Map<String, List<Object>> columnValueListMap = columnNameList.stream()
                .map(
                        columnName -> {
                            String dataType = dataTypeMap.get(columnName);
                            List<Object> valueList = rowDataMapList.stream().map(map -> map.get(columnName)).map(value -> convert(dataType, value)).collect(Collectors.toList());
                            return Pair.of(columnName, valueList);
                        }
                )
                .collect(Collectors.toMap(Pair::getFirst, Pair::getSecond, (v1, v2) -> v1));

        List<Object> primaryValueList = columnValueListMap.get(primaryKey);

        String setSql = columnNameList.stream()
                .filter(columnName -> !columnName.equals(primaryKey))
                .map(
                        columnName -> {
                            List<Object> valueList = columnValueListMap.get(columnName);
                            return Streams.zip(primaryValueList.stream(), valueList.stream(), (primary, value) -> " WHEN " + primary + " THEN " + value)
                                    .collect(Collectors.joining(" ",  columnName + " = CASE " + primaryKey, " END"));
                        }
                )
                .collect(Collectors.joining(", "));
        return "UPDATE table_name " + " SET " + setSql + " WHERE " + whereSql;
    }

}
